﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;
using System.Globalization;

namespace UtilityLibrary.Ormapping
{
    public class OracleSqlCreater : ISqlCreater
    {
        #region ISQLCreater 成员

        public string GetSqlSelect(Type type)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");
            sql.Append(ct.TableName);

            return sql.ToString();
        }

        public string GetSqlSelect(Type type, string condition)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");
            sql.Append(ct.TableName);

            sql.Append(" WHERE ");
            sql.Append(condition);

            return sql.ToString();
        }

        public string GetSqlSelect(Type type, IList<string> conditions)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");
            sql.Append(ct.TableName);

            sql.Append(" WHERE 1 = 1");
            foreach (String condition in conditions)
            {
                if (!String.IsNullOrEmpty(condition))
                {
                    sql.Append(" AND ");
                    sql.Append(condition);
                }
            }

            return sql.ToString();
        }

        public string GetSqlPagedSelect(Type type, int pageSize, int pageIndex)
        {
            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            sql.Append("SELECT TOP ");
            sql.Append(pageSize.ToString("D", CultureInfo.CurrentCulture));
            sql.Append(" * FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE 1=1 ");

            if (pageIndex > 1)
            {
                foreach (PropertyInfo p in type.GetProperties())
                {
                    //获取属性的表字段信息
                    ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                    if (pt == null) continue;

                    if (pt.IsPKValue)
                    {
                        sql.Append(" AND ");
                        sql.Append(pt.FieldName);
                        sql.Append(" NOT IN (SELECT TOP ");
                        sql.Append(Convert.ToString(pageSize * (pageIndex - 1), CultureInfo.CurrentCulture));
                        sql.Append(" ");
                        sql.Append(pt.FieldName);
                        sql.Append(" FROM ");
                        sql.Append(ct.TableName);

                        sql.Append(")");
                    }
                }
            }

            return sql.ToString();
        }

        public string GetSqlPagedSelect(Type type, int pageSize, int pageIndex, String condition)
        {
            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            sql.Append("SELECT TOP ");
            sql.Append(pageSize.ToString("D", CultureInfo.CurrentCulture));
            sql.Append(" * FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE 1=1 ");

            if (pageIndex > 1)
            {
                foreach (PropertyInfo p in type.GetProperties())
                {
                    //获取属性的表字段信息
                    ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                    if (pt == null) continue;
                    if (!pt.IsPKValue) continue;

                    sql.Append(" AND ");
                    sql.Append(pt.FieldName);
                    sql.Append(" NOT IN (SELECT TOP ");
                    sql.Append(Convert.ToString(pageSize * (pageIndex - 1), CultureInfo.CurrentCulture));
                    sql.Append(" ");
                    sql.Append(pt.FieldName);
                    sql.Append(" FROM ");
                    sql.Append(ct.TableName);
                    sql.Append(" WHERE 1=1 ");
                    if (!String.IsNullOrEmpty(condition))
                    {
                        sql.Append(" AND ");
                        sql.Append(condition);
                    }
                    sql.Append(")");
                }
            }

            if (!String.IsNullOrEmpty(condition))
            {
                sql.Append(" AND ");
                sql.Append(condition);
            }

            return sql.ToString();
        }

        public string GetSqlCount(Type type)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(*) FROM ");
            sql.Append(ct.TableName);

            return sql.ToString();
        }

        public string GetSqlCount(Type type, string condition)
        {
            if (condition != null)
            {
                return GetSqlCount(type) + " WHERE " + condition;
            }
            return GetSqlCount(type);
        }

        public string GetSqlCount(Type type, IList<String> conditions)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(GetSqlCount(type));
            if (conditions != null)
            {
                sql.Append(" WHERE 1 = 1 ");
                foreach (String condition in conditions)
                {
                    if (!String.IsNullOrEmpty(condition))
                    {
                        sql.Append(" AND ");
                        sql.Append(condition);
                    }
                }
            }

            return sql.ToString();
        }

        public string GetSqlInsert(Type type)
        {
            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            sql.Append("INSERT INTO ");
            sql.Append(ct.TableName);
            sql.Append(" (");
            StringBuilder fields = new StringBuilder();
            StringBuilder values = new StringBuilder();

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (!pt.IsIndex)
                {
                    fields.Append("[" + pt.FieldName + "],");
                    values.Append(ParamName(pt.FieldName) + ",");
                }
            }
            fields.Remove(fields.Length - 1, 1);
            values.Remove(values.Length - 1, 1);
            sql.Append(fields.ToString());
            sql.Append(") VALUES (");
            sql.Append(values);
            sql.Append(");");
            return sql.ToString();
        }

        public string GetSqlInsert(Type type, int listCount)
        {
            if (listCount <= 0) throw new SqlCreateException("需要修改的对象集合为空");

            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            for (int i = 0; i < listCount; i++)
            {
                sql.Append("INSERT INTO ");
                sql.Append(ct.TableName);
                sql.Append(" (");
                StringBuilder fields = new StringBuilder();
                StringBuilder values = new StringBuilder();

                foreach (PropertyInfo p in type.GetProperties())
                {
                    //获取属性的表字段信息
                    ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                    if (pt == null) continue;

                    if (!pt.IsIndex)
                    {
                        fields.Append("[" + pt.FieldName + "],");
                        values.Append(ParamName(pt.FieldName) + i.ToString("D", CultureInfo.CurrentCulture) + ",");
                    }
                }

                fields.Remove(fields.Length - 1, 1);
                values.Remove(values.Length - 1, 1);
                sql.Append(fields.ToString());
                sql.Append(") VALUES (");
                sql.Append(values);
                sql.Append(");");
            }

            return sql.ToString();
        }

        public string GetSqlGetIdentity(Type type)
        {
            StringBuilder sql = new StringBuilder();
            String PKValueField = "";
            String IndexField = "";

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (pt.IsIndex)
                {
                    IndexField = pt.FieldName;
                }
                if (pt.IsPKValue)
                {
                    PKValueField = pt.FieldName;
                }
            }

            if (!String.IsNullOrEmpty(PKValueField) && !String.IsNullOrEmpty(IndexField))
            {
                sql.Append("SELECT ");
                sql.Append(PKValueField);
                sql.Append(" FROM ");
                sql.Append(ct.TableName);
                sql.Append(" WHERE ");
                sql.Append(IndexField);
                sql.Append(" IN (SELECT MAX(");
                sql.Append(IndexField);
                sql.Append(") FROM ");
                sql.Append(ct.TableName);
                sql.Append(")");
            }

            return sql.ToString();
        }

        public string GetSqlUpdate(Type type)
        {
            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            sql.Append("UPDATE ");
            sql.Append(ct.TableName);
            sql.Append(" SET ");

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (!pt.IsPKValue && !pt.IsIndex)
                {
                    sql.Append("[" + pt.FieldName + "]");
                    sql.Append(" = ");
                    sql.Append(ParamName(pt.FieldName) + ",");
                }
            }
            sql.Remove(sql.Length - 1, 1);

            sql.Append(" WHERE 1=1 ");
            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (pt.IsPKValue)
                {
                    sql.Append(" AND ");
                    sql.Append("[" + pt.FieldName + "]");
                    sql.Append(" = ");
                    sql.Append(ParamName(pt.FieldName));
                }
            }

            return sql.ToString();
        }

        public string GetSqlUpdate(Type type, Int32 listCount)
        {
            if (listCount <= 0) throw new SqlCreateException("需要删除的对象集合为空");

            StringBuilder sql = new StringBuilder();

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            for (int i = 0; i < listCount; i++)
            {
                sql.Append("UPDATE ");
                sql.Append(ct.TableName);
                sql.Append(" SET ");

                foreach (PropertyInfo p in type.GetProperties())
                {
                    //获取属性的表字段信息
                    ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                    if (pt == null) continue;

                    if (!pt.IsPKValue && !pt.IsIndex)
                    {
                        sql.Append("[" + pt.FieldName + "]");
                        sql.Append(" = ");
                        sql.Append(ParamName(pt.FieldName) + ",");
                    }
                }
                sql.Remove(sql.Length - 1, 1);

                sql.Append(" WHERE 1=1 ");
                foreach (PropertyInfo p in type.GetProperties())
                {
                    //获取属性的表字段信息
                    ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                    if (pt == null) continue;

                    if (pt.IsPKValue)
                    {
                        sql.Append(" AND ");
                        sql.Append("[" + pt.FieldName + "]");
                        sql.Append(" = ");
                        sql.Append(ParamName(pt.FieldName) + i.ToString("D", CultureInfo.CurrentCulture));
                    }
                }
                sql.Append(" ;");
            }

            return sql.ToString();
        }

        public string GetSqlDelete(Type type)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE 1=1");

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (pt.IsPKValue)
                {
                    sql.Append(" AND ");
                    sql.Append(pt.FieldName);
                    sql.Append(" = ");
                    sql.Append(ParamName(pt.FieldName));
                }
            }

            return sql.ToString();
        }

        public string GetSqlDelete(Type type, Int32 listCount)
        {
            if (listCount <= 0) throw new SqlCreateException("需要删除的对象集合为空");

            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE ");

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (pt.IsPKValue)
                {
                    sql.Append(pt.FieldName);
                    sql.Append(" IN (");
                    for (int i = 0; i < listCount; i++)
                    {
                        sql.Append(ParamName(pt.FieldName) + i.ToString("D", CultureInfo.CurrentCulture));
                        sql.Append(",");
                    }
                    sql.Remove(sql.Length - 1, 1);
                    sql.Append(")");
                }
            }

            return sql.ToString();
        }

        public string GetSqlConditionDelete(Type type, string condition)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();

            sql.Append("DELETE FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE ");
            sql.Append(condition);

            return sql.ToString();
        }

        public string GetSqlConditionDelete(Type type, IList<String> conditions)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();

            sql.Append("DELETE FROM ");
            sql.Append(ct.TableName);

            sql.Append(" WHERE 1 = 1");
            foreach (String condition in conditions)
            {
                if (!String.IsNullOrEmpty(condition))
                {
                    sql.Append(" AND ");
                    sql.Append(condition);
                }
            }

            return sql.ToString();
        }

        public string GetSqlGetDetails(Type type)
        {
            //获得实体类描述信息
            ModelClassAttribute ct = ModelAttributeOperator.GetModelClassAttribute(type);

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");
            sql.Append(ct.TableName);
            sql.Append(" WHERE 1=1");

            foreach (PropertyInfo p in type.GetProperties())
            {
                //获取属性的表字段信息
                ModelPropertyAttribute pt = ModelAttributeOperator.GetModelPropertyAttribute(p);
                if (pt == null) continue;

                if (pt.IsPKValue)
                {
                    sql.Append(" AND ");
                    sql.Append(pt.FieldName);
                    sql.Append(" = ");
                    sql.Append(ParamName(pt.FieldName));
                }
            }

            return sql.ToString();
        }

        //public IDataParameter CreateInputParam(string fieldName, object value, bool isNullable)
        //{
        //    throw new NotImplementedException();
        //}

        public IDataParameter CreateInputOutputParam(string fieldName, object value, bool isNullable)
        {
            SqlParameter newParam = new SqlParameter(ParamName(fieldName), value);
            newParam.Direction = ParameterDirection.InputOutput;
            newParam.IsNullable = isNullable;
            return newParam;
        }

        //public IDataParameter CreateOutputParam(string fieldName, String dbType)
        //{

        //    SqlParameter newParam = new SqlParameter(ParamName(fieldName), SqlDbType.Int);
        //    newParam.Direction = ParameterDirection.Output;
        //    return newParam;
        //}

        //public IDataParameter CreateReturnValueParam(string fieldName)
        //{
        //    throw new NotImplementedException();
        //}

        public string ParamName(string fieldName)
        {
            return "@" + fieldName;
        }

        #endregion
    }
}
